package com.joinway.framework.admin.core.service.file;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.regex.Pattern;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.WorkbookUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.joinway.framework.admin.core.bean.FileImportContext;
import com.joinway.framework.bean.type.Server;
import com.joinway.framework.core.repository.SystemConfigRepository;
import com.joinway.framework.extension.groovy.GroovyUtils;
import com.joinway.framework.extension.utils.DataUtils;


@Service
public class ExcelProcessor extends AbstractFileProcessor {

	private final static Logger log = LoggerFactory.getLogger(ExcelProcessor.class);
	
	final static String DEFAULT_SHEET_NAME = "Sheet1";
	final static int DEFAULT_MAX_COLUMN_WIDTH = 80;
	final static int DEFAULT_COLUMN_WIDTH_DELT = 3;
	
	final static Pattern datePattern = Pattern.compile("\\d{4}-\\d{1,2}-\\d{1,2}\\s+\\d{1,2}:\\d{1,2}:\\d{1,2}(\\.\\d+)?");
	
	@Autowired SystemConfigRepository systemRepository;
	
	@Override
	public String export(String fileName, Map<String, String> columns, List<?> domains, int port) throws Exception {
		String sheetName = StringUtils.isBlank(fileName) ? 
				DEFAULT_SHEET_NAME : 
				WorkbookUtil.createSafeSheetName(StringUtils.trimToEmpty(fileName));
		
		Workbook wb = new HSSFWorkbook();
		
		Sheet sheet = wb.createSheet(sheetName);
		Row titleRow = sheet.createRow(0);
		
		int columnIndex = 0;
		int columnCount = 0;
		
		CellStyle style = getBorderStyle(wb);
		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
		
		List<Integer> maxLength = new ArrayList<>();
		
		// add header
		Iterator<String> columnNames = columns.keySet().iterator();
		while(columnNames.hasNext()){
			String columnName = columnNames.next();
			String title = columns.get(columnName);
			
			Cell cell = titleRow.createCell(columnIndex);
			
			cell.setCellStyle(style);
			cell.setCellValue(title);
			
			columnCount++;
			
			maxLength.add(title.getBytes().length);
			
			columnIndex++;
		}
		
		if(domains != null){
			// add data
			for(int i = 0; i < domains.size(); i++){
				Row dataRow = sheet.createRow(i + 1);
				
				columnIndex = 0;
				
				Object row = domains.get(i);
				
				columnNames = columns.keySet().iterator();
				while(columnNames.hasNext()){
					String columnName = columnNames.next();
					
					Object value = GroovyUtils.getProperty(row, columnName);
					String text = getExportTextValue(columnName, columnIndex, value);
							
					Cell cell = dataRow.createCell(columnIndex);
					
					cell.setCellStyle(style);
					
					cell.setCellValue(text);
					
					int len = text.getBytes().length;
					
					if(len > maxLength.get(columnIndex)){
						maxLength.set(columnIndex, len);
					}
						
					columnIndex++;
				}
			}
			
			// auto filter
			sheet.setAutoFilter(new CellRangeAddress(0, domains.size(), 0, columnCount - 1));
		}
		
		// resize column
		int delt = systemRepository.getIntValue(SystemConfigRepository.ExcelColumnWithDelt);
		delt = delt == 0 ? DEFAULT_COLUMN_WIDTH_DELT : delt;
		
		int maxWidth = systemRepository.getIntValue(SystemConfigRepository.ExcelColumnMaxWidth);
		maxWidth = (maxWidth == 0 ? DEFAULT_MAX_COLUMN_WIDTH : maxWidth) * 256;
		
		for(int i = 0; i < columnCount; i++){
//			sheet.autoSizeColumn(i);
			int width = (maxLength.get(i) + delt) * 256;
			width = width > maxWidth ? maxWidth : width;
			sheet.setColumnWidth(i, width);
		}
		
		// freeze
		sheet.createFreezePane(0, 1);
		
		String localfileName = sheetName + "." + FileConstants.FileType.Excel2003;
		
		String url = FileManager.save(FileConstants.FileDir.Download + "/" + UUID.randomUUID().toString(), localfileName, Server.Web, port, new IFileRepository<String>(){
			@Override
			public void save(String file, String context) throws Exception {
				FileOutputStream fos = new FileOutputStream(file);
				wb.write(fos);
				fos.close();
			}
		});
		log.info("generated {} rows to excel file url {}", DataUtils.getSize(domains), url);
		
		return url;
	}

	@Override
	public FileImportContext imports(File file, String table, String columnMap) throws Exception {
		Workbook wb = new HSSFWorkbook(new FileInputStream(file));
		Sheet sheet = wb.getSheetAt(0);
		Row titleRow = sheet.getRow(0);
		
		Map<String, String> map = getColumnMap(columnMap);
		List<String> columns = new ArrayList<>();
		
		int index = 0;
		Cell cell = titleRow.getCell(index);
		String column = cell == null ? "" : StringUtils.trimToEmpty(cell.getStringCellValue());
		//log.debug("column is [{}]", column);
		
		while(column.length() > 0){
			columns.add(map.get(column));
			
			cell = titleRow.getCell(++index);
			column = cell == null ? "" : StringUtils.trimToEmpty(cell.getStringCellValue());
		}
		
		FileImportContext context = new FileImportContext();
		
		if(CollectionUtils.isNotEmpty(columns)){
			int columnCount = columns.size();
			index = 1;	// for data row
			Row dataRow = sheet.getRow(index);
			List<List<String>> records = new ArrayList<>();
			
			while(dataRow != null){
				List<String> row = new ArrayList<>();
				String rowValue = "";
				
				for(int i = 0; i < columnCount; i++){
					cell = dataRow.getCell(i);
					
					String text = getCellValue(cell);
					text = getImportTextValue(columns.get(i), i, text);
					rowValue += text;
					row.add(text);
				}
				
				if(rowValue.length() == 0){
					// got empty rows
					break;
				}
				records.add(row);
				
				dataRow = sheet.getRow(++index);
			}
			
			context = super.imports(columns, records, table);
		}else{
			context = super.createDefaultFileImportContext();
		}
		
		return context;
	}

	@Override
	public String getFileType() {
		return FileConstants.FileType.Excel2003;
	}

	CellStyle getBorderStyle(Workbook wb){
		CellStyle style = wb.createCellStyle();
		
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    
	    style.setTopBorderColor(HSSFColor.BLACK.index);
	    style.setBottomBorderColor(HSSFColor.BLACK.index);
	    style.setLeftBorderColor(HSSFColor.BLACK.index);
	    style.setRightBorderColor(HSSFColor.BLACK.index);
	    
	    return style;
	}

	protected String getCellValue(Cell cell){
		String value = "";
		
		if(cell != null){
			switch(cell.getCellType()){
				case Cell.CELL_TYPE_BOOLEAN:
					value = String.valueOf(cell.getBooleanCellValue());
					break;
				case Cell.CELL_TYPE_NUMERIC:
					if(HSSFDateUtil.isCellDateFormatted(cell)){
						// 日期格式：处理yyyy-MM-dd, d/m/yyyy h:mm, HH:mm 等不含文字的日期格式
						String format = null;
						if(cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")){
							format = "HH:mm";
						}else if(cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm:ss")){
							format = "HH:mm:ss";
						}else{
							format = "yyyy-MM-dd HH:mm:ss";
						}
						Date date = cell.getDateCellValue();
						value = DateFormatUtils.format(date, format);
					}else if(cell.getCellStyle().getDataFormat() == 58){
						// 自定义日期格式：处理yyyy年m月d日,h时mm分,yyyy年m月等含文字的日期格式
						double v = cell.getNumericCellValue();
						Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(v);
						value = DateFormatUtils.format(date, "yyyy-MM-dd HH:mm:ss");
					}else{
//						double v = cell.getNumericCellValue();
//						DecimalFormat format = new DecimalFormat(); 
//						String temp = cell.getCellStyle().getDataFormatString();
//						if("General".equals(temp)){
//							if()
//							format.applyPattern("#");
//						}
//						value = format.format(v);
//						value = StringUtils.replace(value, ",", "");
						BigDecimal decimal = new BigDecimal(cell.getNumericCellValue());
						value = decimal.toPlainString();
//						v = StringUtils.replace(v, ",", "");
//						if(v.contains(".")){
//							String[] arr = StringUtils.split(v, ".");
//							String dec = arr[1];
//							if(Integer.valueOf(dec) == 0){
//								value = arr[0];
//							}else{
//								value = v;
//							}
//						}
					}
//					value = String.valueOf(cell.getNumericCellValue());
					break;
				case Cell.CELL_TYPE_STRING:
					value = cell.getStringCellValue();
					break;
				case Cell.CELL_TYPE_FORMULA:
					value = cell.getStringCellValue();
					break;
				case Cell.CELL_TYPE_ERROR:
					value = String.valueOf(cell.getErrorCellValue());
					break;
			}
		}
		
		return StringUtils.trimToEmpty(value);
	}
}


